package com.lsege.service.backstage;

import com.lsege.entity.backstage.Bike;
import com.lsege.entity.backstage.VoBike;
import com.lsege.mapper.backstage.BikeMapper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * Created by lsd on 2017-06-27.
 */
@Service
public class BikeServiceImpl implements BikeService {
    @Autowired
    BikeMapper bikeMapper;
    @Override
    public void addBikeInfo(Bike bike) {
        bikeMapper.addBikeInfo(bike);
    }

    @Override
    public Long selectBikeCode(String bikeCode) {
        return bikeMapper.selectBikeCode(bikeCode);
    }

    @Override
    public List<VoBike> getBikeList() {
        return bikeMapper.getBikeList();
    }

    @Override
    public Long getBikeListNum() {
        return bikeMapper.getBikeListNum();
    }

    @Override
    public void deleteBike(String bicycleCode) {
        bikeMapper.deleteBike(bicycleCode);
    }

    @Override
    public String addBatchBike(MultipartFile file) throws IOException, InvalidFormatException {
        String  ret="";
        Workbook workBook = WorkbookFactory.create(file.getInputStream());
        int sheetNum=workBook.getNumberOfSheets();
        ero:for (int i=0;i<sheetNum;i++){
            Sheet sheet=workBook.getSheetAt(i);
            for (int j=1;j<=sheet.getLastRowNum();j++){
                String st=getLine(sheet,j);
                String[] arrayList=st.split(";");
                if (bikeMapper.selectBikeCode(arrayList[0])==0){
                    Bike bike=new Bike();
                    String block_UUID = UUID.randomUUID().toString().replaceAll("-", "");
                    bike.setBlockUUID(block_UUID);
                    bike.setBicycleCode(arrayList[0]);
                    bike.setPasswordTable(arrayList[1]);
                    bike.setBlockMAC(arrayList[2]);
                    bike.setPlatformCode(Long.parseLong(arrayList[3]));
                    bike.setClientAccount(arrayList[4]);
                    bike.setInnerCity(arrayList[5]);
                    bikeMapper.addBikeInfo(bike);
                }else {
                    ret=arrayList[0];
                    break ero;
                }

            }

        }
        return ret;
    }

    @Override
    public List<VoBike> getBikeByCity(VoBike voBike) {
        return bikeMapper.getBikeByCity(voBike);
    }

    @Override
    public Long getBikeByCityCount(VoBike voBike) {
        return bikeMapper.getBikeByCityCount(voBike);
    }

    @Override
    public List<VoBike> getBikeByAccount(VoBike voBike) {
        return bikeMapper.getBikeByAccount(voBike);
    }

    @Override
    public Long getBikeByAccountCount(VoBike voBike) {
        return bikeMapper.getBikeByAccountCount(voBike);
    }

    public static String getLine(Sheet sheet, int row) {
            // 根据行数取得Sheet的一行
            // 创建字符创缓冲区
            StringBuffer buffer = new StringBuffer();
            try{
                Row rowline = sheet.getRow(row);
                // 获取当前行的列数
                int filledColumns = rowline.getLastCellNum();
                Cell cell = null;
                // 循环遍历所有列
                for (int i = 0; i <= 5; i++) {
                    // 取得当前Cell
                    cell = rowline.getCell((short) i);
                    String cellvalue = null;

                    if (cell != null) {
                        // 判断当前Cell的Type
                        switch (cell.getCellType()) {

                            // Cell的Type为NUMERIC
                            case HSSFCell.CELL_TYPE_NUMERIC: {
                                // 判断当前的cell是否为Date
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    // 如果是Date类型则，取得该Cell的Date值
                                    Date date = cell.getDateCellValue();
                                    // 把Date转换成本地格式的字符串
                                    cellvalue = cell.getDateCellValue().toLocaleString();
                                } else { // 如果是纯数字
                                    // 取得当前Cell的数值,强制转换为int
                                    Long num = new Long(
                                            (long) cell.getNumericCellValue());
                                    cellvalue = String.valueOf(num);
                                }
                                break;
                            }
                            // Cell的Type为STRING
                            case HSSFCell.CELL_TYPE_STRING:
                                // 取得当前的Cell字符串
                                cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                                break;
                            // 默认的Cell值
                            default:
                                cellvalue = "nu";
                        }
                    } else {
                        cellvalue = "nu";
                    }
                    // 在每个字段之间插入分割符
                    buffer.append(cellvalue).append(";");
                    // 去除最后一个分隔符
                    buffer.substring(0, buffer.length() - 1);
                }
            }catch (Exception e){
                return  "cols";
            }
            return buffer.toString();
        }

//    public static int findRealRows(Sheet sheet, int... flag) {
//        int row_real = 0;
//        int rows = sheet.getPhysicalNumberOfRows();// 此处物理行数统计有错误,
//        int size = flag.length;
//        try {
//
//
//            for (int i = 1; i < rows; i++) {
//                Row row = sheet.getRow(i);
//                int total = 0;
//                ArrayList<Integer> blank =new ArrayList<Integer>();
//                int type=-1;
//                String s = null;
//                for(int j:flag){
//                    if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){
//                        type=row.getCell(j).getCellType();
//                        row.getCell(j).setCellType(1);
//                    }
//
//                    if (row.getCell(j) == null||row.getCell(j).getStringCellValue().matches("^\\s+$")||row.getCell(j).getCellType()>2) {
//                        total++;
//
//                        if(!(row.getCell(j) == null)&&row.getCell(j).getCellType()<2){
//                            row.getCell(j).setCellType(type);
//                        }
//                        blank.add(j);
//
//                    }
//                }
//                System.out.println(s+"我");
//                // 如果4列都是空说明就该返回
//                if (total == flag.length) {
//
//                    return row_real;
//                } else if (total == 0) {
//                    row_real++;
//
//                } else {
//                    String h="";
//                    for(Integer b:blank){
//
//                        h=h+"第"+(b+1)+"列"+" ";
//                    }
////                    throw new BusinessException("第" + (i + 1) + "行" + h
////                            + "不能为空");
//                }
//
//            }
//        } catch (NullPointerException e) {
////            throw new BusinessException("excel格式异常,请检查excel格式有无数据缺失,无效数据行!");
//        }
//        return row_real;
//    }
}
